import json
from decimal import Decimal
import decimal
import mysql.connector
import requests

mydb = mysql.connector.connect(
    host='172.16.33.205',
    user='game_admin',
    password='bHYACbxgM2gWHnF6Il1nX8VnNEfb51KY',
    port='3307',
    database='ad_report'
)

# 获取游标
cursor = mydb.cursor()

# 跟踪转化下的数据查询———>今日头条————>（机动战姬）

url = 'http://uat-ad-tracking-api-sh.bilibili.co/stat/date/group?game_id=3992&start_date=2021-01-01&end_date=2021-09-30&is_display_ineffective=2&tab_type=2&channel_id=1&group_name=&page_no=1&page_size=20&sort=&sort_type=0&request-id=BXofRFkla2BdfwFYuedcLVgDq3TCeR1r'
header={'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.159 Safari/537.36',
        'Cookie': '_AJSESSIONID=2ee7ac401cd06f2ecb5fe33d9a0b0a7c; username=zhangyu16'}
r = requests.get(url,headers = header)
result = r.json()

# 媒体曝光量总计
def impression():
    cursor.execute('SELECT SUM(impression) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result = cursor.fetchall()
    print('媒体曝光量总计为：',result[0][0])
    result_imperssion=result[0][0]
    return result_imperssion
impression()

# 媒体曝光量下某一行数据(0525机动公测心景-投放管家站内付费-安卓)
def impression_s():
    cursor.execute('SELECT SUM(impression) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ad_group_id=108285 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result = cursor.fetchall()
    print('0525机动公测心景-投放管家站内付费-安卓-->媒体曝光量为：',result[0][0])
impression_s()


# 媒体点击量总计
def click_creative():
    cursor.execute('SELECT SUM(click) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_click_creative = cursor.fetchall()
    #print('媒体点击量总计为：',result_click_creative[0][0])
    s1 = result_click_creative[0][0]
    return s1
click_creative()

s_click_creativa = result['data']['total']['media_click']
assert click_creative()==s_click_creativa,'数据错误'


# 媒体点击量下某一行数据(0525机动公测心景-投放管家站内付费-安卓)
def click_creative_s():
    cursor.execute('SELECT SUM(click) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ad_group_id=108285 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_click_creative_s = cursor.fetchall()
    print('0525机动公测心景-投放管家站内付费-安卓-->媒体点击量为：',result_click_creative_s[0][0])
    s2 = result_click_creative_s[0][0]
    return s2
click_creative_s()


# 媒体消耗总计
def cost():
    cursor.execute('SELECT SUM(cost) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result = cursor.fetchall()
    print('媒体消耗总计为：',result[0][0])
cost()

# 媒体消耗下某一行数据(0525机动公测心景-投放管家站内付费-安卓)
def cost_s():
    cursor.execute('SELECT SUM(click) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ad_group_id=108285 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result = cursor.fetchall()
    print('0525机动公测心景-投放管家站内付费-安卓-->媒体消耗为：',result[0][0])
cost_s()


# 媒体激活量总计
def activate():
    cursor.execute('SELECT SUM(activate) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_activate = cursor.fetchall()
    print('媒体激活量总计为：',result_activate[0][0])
    s3 = result_activate[0][0]
    return s3
activate()

# 媒体激活量下某一行数据(0525机动公测心景-投放管家站内付费-安卓)
def activate_s():
    cursor.execute('SELECT SUM(activate) FROM ad_creative_data_report WHERE game_id=3992 AND ad_channel_id=1 AND ad_group_id=108285 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result_activate_s = cursor.fetchall()
    print('0525机动公测心景-投放管家站内付费-安卓-->媒体激活量为：',result_activate_s[0][0])
    s4 = result_activate_s[0][0]
    return s4
activate_s()


# 媒体激活率总计
def activate_ratio():
    print('媒体激活率总计为：%.2f%%'%(activate()/click_creative()*100))
activate_ratio()

# 媒体激活率下某一行数据(0525机动公测心景-投放管家站内付费-安卓)
def activate_ratio_s():
    print('0525机动公测心景-投放管家站内付费-安卓-->媒体激活率为：%.2f%%'%(activate_s()/click_creative_s()*100))
activate_ratio_s()


# 点击量总计
def click():
    cursor.execute('SELECT SUM(click) FROM ad_ds_report_group WHERE game_id=3992 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result = cursor.fetchall()
    #print('点击量总计为：',result[0][0])
    s00 = result[0][0]
    return s00
click()

s_click = result['data']['total']['click']
assert click()==click(),'数据错误'


# 点击量下某一行数据（0408cb西瓜-抖音-and-ZY3（2862））
def click_r():
    cursor.execute('SELECT SUM(click) FROM ad_ds_report_group WHERE game_id=3992 AND ad_group_id=102570 AND ad_channel_id=1 AND ds BETWEEN "2021-01-01" AND "2021-09-30";')
    result = cursor.fetchall()
    print('西瓜_抖音(2862)点击量为：',result[0][0])
click_r()


# 独立点击量总计





# 关闭数据库连接
cursor.close()
mydb.close()
